#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_

import time, sys
sys.path.insert(0, '../apps')
from charts.models import *
#from charts.models import *
from departments.models import *
import datetime
from datetime import date


db = get_connection('sqlite:///../database.db', debug=False)
meta = MetaData(db)
db.metadata.reflect()


#从settings.ini获取任务类型编号
settings = Ini('../apps/settings.ini')

assignment_type = dict(settings.PARA.ASSIGNMENT_TYPE)
#print assignment_type
#q,w,e = assignment_type
#print q,w,e
#for x in assignment_type:
#    print x
#    num,name = x
#    print num,name
#    if name == '任务':
#        assi_type = num
#        print '任务',assi_type
#设置任务类型
#assi_type = 1
#准开发需求相关报表数据处理

req = db.metadata.tables['assignment']
#任务明细表数据处理,后续根据任务明细表实际表名修改

req_mx = db.metadata.tables['assignment_mx']

#从requirement_kfzx_kfzxs表获取kfzx_id
#使用以下方式连接requirement_kfzx_kfzxs表
#req_kfzx = db.metadata.tables['requirement_kfzx_kfzxs']                                                      
#tx_table = db.metadata.tables['requirments_tx_count']                      


#获取报表当前月第一天
def getMonth(): 
    return str(datetime.date.today())[5:7] 
def strtodatetime(datestr,format):       
    return datetime.datetime.strptime(datestr,format)   
def getYear():   
    return str(datetime.date.today())[0:4] 

format="%Y-%m-%d" 


#获取当前月份 是一个字符串    
year =  getYear()
month = getMonth()
oneday = datetime.timedelta(days=1)

#转换当前月份的显示格式
month_view = str(month) + '月'

#判断本月末是否是季度末
#quater = long(month)
#if(quater < 3) and (quater <> 3) and (quater <> 6) and (quater <> 9) and (quater <> 12):
#    sys.exit()

#next_month是季度末日期，即为本月末日期
next_month = long(month)+1
#判断当前月是否为12月，若为12月则月份赋值1月，年度增加1年
next_year = str(year)
if(next_month == 13):
    next_month = 1
    next_year = str(long(year)+1)
next_month = str(next_month)

#计算季度初始日期，即为本季度第一个月1日
#first_month = long(month) - 2
first_month = long(month)
first_month = str(first_month)
first_day = year + '-' + first_month + '-01'
last_day = next_year + '-' + next_month + '-01'

month_first_day = strtodatetime(first_day,format)
month_last_day = strtodatetime(last_day,format)-oneday

#print month_last_day
#计算本月新增准开发需求任务数，算法：开始日期小于或等于本月末and大于等于本月1日
#new = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)]).group_by(req.c.kfzx)

new = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],and_(req.c.release_date>=month_first_day,req.c.release_date<=month_last_day)).group_by(req.c.kfzx,req.c.type)

#old = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],and_(req.c.release_date <= month_first_day,~req.c.stage.in_(['17','18']))).group_by(req.c.kfzx,req.c.type)
#old = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],and_(req.c.release_date<month_first_day,req_mx.c.modified_date<month_last_day,req_mx.c.modified_date>=month_first_day,req.c.stage.in_(['17','18']),req_mx.c.ass_id == req.c.ass_id,req_mx.c.stage == req.c.stage)).group_by(req.c.kfzx,req.c.type)

#old = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],or_(and_(req.c.release_date <= month_first_day,~req.c.stage.in_(['17','18'])),and_(req.c.release_date<month_first_day,req_mx.c.modified_date<month_last_day,req_mx.c.modified_date>=month_first_day,req.c.stage.in_(['17','18']),req_mx.c.ass_id == req.c.ass_id,req_mx.c.stage == req.c.stage))).group_by(req.c.kfzx,req.c.type)

#old = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],
#            or_(and_(req.c.release_date <= month_first_day,
#                     ~req.c.stage.in_(['17','18'])
#                ),
#                and_(req.c.release_date<month_first_day,
#                     req.c.stage.in_(['17','18']),
#                     req_mx.c.modified_date<month_last_day,
#                     req_mx.c.modified_date>=month_first_day,
#                     req_mx.c.ass_id == req.c.ass_id,
#                     req_mx.c.stage == req.c.stage
#                     )
#                )).group_by(req.c.kfzx,req.c.type)

#统计续建任务中本月状态为未完成或异常终止的任务
old1 = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],
               and_(req.c.release_date <= month_first_day,
                     ~req.c.stage.in_(['17','18'])
                     )
              ).group_by(req.c.kfzx,req.c.type)

#统计续建任务中任务状态在本月修改为完成或异常终止的任务
old2 = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)],
                and_(req.c.release_date<month_first_day,
                     req.c.stage.in_(['17','18']),
                     req_mx.c.modified_date<month_last_day,
                     req_mx.c.modified_date>=month_first_day,
                     req_mx.c.ass_id == req.c.ass_id,
                     req_mx.c.stage == req.c.stage
                     )
               ).group_by(req.c.kfzx,req.c.type)


new_rs = new.execute()
old1_rs = old1.execute()
old2_rs = old2.execute()


#定义字典型data
data = {}
#连接kfzx表,根据开发中心id初始化data
req_tx_model = get_model('kfzx')
sel_tx = select([req_tx_model.c.id])


for x in assignment_type:
#    print x
    data[x] ={}
    tx_dic = sel_tx.execute()
    for stages in tx_dic:
        tx, = stages
        y = 'new_num_%02d' % tx
        data[x][y] = 0
        y = 'old_num_%02d' % tx
        data[x][y] = 0
#        print x,y,data[x][y]
    data[x]['month_date'] = month_last_day
    data[x]['month'] = month_view
    data[x]['type'] = x

#data['total_num'] = 0
    

#以下两个for语句完成new_rs，old_rs中新增和续建准开发需求放在一条记录中                                                            
    
for row in new_rs:
        tx,in_type,count = row
        table_row = 'new_num_%02d' % tx
        data[in_type][table_row] = count
        data[in_type]['type'] = in_type       
for row in old1_rs:
        tx,in_type,count = row
#        print 'old',count
        table_row = 'old_num_%02d' % tx
#        print in_type,table_row
#        data[in_type][table_row] += count
#        print 'data[in_type][table_row]',data[in_type][table_row]
        old_count = data[in_type][table_row]
#        count += old_count
        data[in_type][table_row] += count
        data[in_type]['type'] = in_type
        data[in_type]['kfzx'] = tx

for row in old2_rs:
        tx,in_type,count = row
#        print 'old',count
        table_row = 'old_num_%02d' % tx
#        data[in_type][table_row] += count
        data[in_type][table_row] += count
        data[in_type]['type'] = in_type
        data[in_type]['kfzx'] = tx

        

#将本月准开发需求汇总数据赋值给total_num


req_model = get_model('assignment_month_report_kfzx')

#插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
#db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
req_model.remove(req_model.c.month_date == month_last_day)
#print data
# 按照任务类型存储统计数据
for t,v in data.items():
    r = req_model(**v)
    r.save()


    
        
    
    

